#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date:
# Update Date:
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'
"""Function for GreenPlum DB tables partitions manager - add partitions.

Add tables partitions.

  ClassFoo: None.
  functionBar(): None.
  python pyinstaller.py -F /home/gpadmin/crontabshell/partition_add.py
"""

import sys
import platform
import colorama
import dsn

if platform.system() == 'Windows' or platform.system() == 'Darwin':
    from psycopg2 import *
elif platform.system() == 'Linux':
    from pygresql.pgdb import *
import time
import datetime
import partitioncom

begin_time = datetime.datetime.now()

try:
    print 'The Database Connecting...'
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
except Exception, e:
    print 'DATABASE Connection Error,Pls Check the db info:[' + dsn.host, dsn.database, dsn.user + ']'
    sys.exit(1)

print "#" * 80
print "#", (colorama.Fore.RED + "GPDB Partition Manager(+PA) For BOMC".center(76)), (colorama.Fore.RESET + "#")
print "#", (colorama.Fore.GREEN + "Processing".center(76)), (colorama.Fore.RESET + "#")
print "#" * 80
print "+" + "-" * 26 + "+" + "-" * 5 + "+" + "-" * 45 + "+"
print "|" + (colorama.Fore.YELLOW + "Timestapm".center(26)) + (colorama.Fore.RESET + "|") + (
    colorama.Fore.YELLOW + "Seq".center(5)) + (colorama.Fore.RESET + "|") + (
          colorama.Fore.YELLOW + "Relation".center(45)) + (colorama.Fore.RESET + "|")
print "+" + "-" * 26 + "+" + "-" * 5 + "+" + "-" * 45 + "+"

try:
    cur = db.cursor()
    cur.execute("select  to_char(now(),'yyyymmddhh24miss');")
    log_id = cur.fetchone()[0]
    sql = "select schemaname,tablename,partlevel,p_type,pk_type_m,pk_type_d, pk_type_h,reserve_m,reserve_d,reserve_h,add_type,default_partition from gpmg.manager_table where debug=0 and (status=1 or (status=0 and expire>current_date)) and (oper_type='all' or oper_type='add') order by id"
    cur.execute(sql)
    serl = 0
    for ret in cur.fetchall():
        v_schemaname = ret[0]
        v_tablename = ret[1]
        v_object = v_schemaname + "." + v_tablename
        sql_insert_log = "insert into gpmg.manager_table_operlog select '" + v_schemaname + "','" + v_tablename + "', now(),'a','" + log_id + "';"
        sql_update_log = "update gpmg.manager_table set add_result=now() where schemaname||'.'||tablename='" + v_object + "';"
        serl += 1
        print "|" + str(datetime.datetime.now()).center(25) + "|" + str(serl).center(5) + "|" + v_object.center(
            45) + "|"

        v_partlevel = ret[2]
        v_p_type = ret[3]
        v_pk_type_m = ret[4]
        v_pk_type_d = ret[5]
        v_pk_type_h = ret[6]
        v_reserve_m = ret[7]
        v_reserve_d = ret[8]
        v_reserve_h = ret[9]
        v_add_type = ret[10]
        v_default_partition = ret[11]
        sql2 = "select count(*) from pg_tables where schemaname||'.'||tablename = '" + v_object + "'"
        cur.execute(sql2)
        is_exists = cur.fetchone()[0]
        if is_exists == 0:
            print "Not exists this table!\n"
            continue
        sql2 = "select count(1) from pg_locks where mode in ('AccessExclusiveLock','ExclusiveLock','RowExclusiveLock') and relation = '" + v_object + "'::regclass and gp_segment_id='-1' and granted=True;"
        is_lock = 0
        for i in range(5):
            cur.execute(sql2)
            is_lock = cur.fetchone()[0]
            if is_lock == 0:
                if i > 0:
                    print ''
                break
            print " sleep 10's ",
            time.sleep(10)
        if is_lock > 0:
            print "Can't lock this table!"
            continue
        if v_add_type == 'a':
            if v_partlevel == 1:
                if (',' + v_p_type + ',').find(',m,') >= 0:
                    if v_pk_type_m == 'timestamp':
                        v_reserve = v_reserve_m
                        partitioncom.partition_add_months_timestamp(v_object, v_reserve)
                        cur.execute(sql_update_log)
                        cur.execute(sql_insert_log)

                if (',' + v_p_type + ',').find(',d,') >= 0:
                    v_reserve = v_reserve_d
                    if v_pk_type_d == 'timestamp':
                        partitioncom.partition_add_day_timestamp(v_object, v_reserve)
                        cur.execute(sql_update_log)
                        cur.execute(sql_insert_log)

                    elif v_pk_type_d == 'integer' or v_pk_type_d == 'bigint' or v_pk_type_d == 'numeric':
                        partitioncom.partition_add_day_integer(v_object, v_reserve)
                        cur.execute(sql_update_log)
                        cur.execute(sql_insert_log)

                if (',' + v_p_type + ',').find(',h,') >= 0:
                    v_reserve = v_reserve_h
                    if v_pk_type_h == 'timestamp':
                        partitioncom.partition_add_hour_timestamp(v_object, v_reserve)
                        cur.execute(sql_update_log)
                        cur.execute(sql_insert_log)

            elif v_partlevel == 2:
                if (',' + v_p_type + ',').find(',d,h,') >= 0:
                    v_reserve = v_reserve_d
                    if v_pk_type_d == 'date':
                        pass
                    elif (v_pk_type_d == 'integer' or v_pk_type_d == 'bigint') and (
                                    v_pk_type_h == 'integer' or v_pk_type_h == 'bigint'):
                        partitioncom.partition_add_day_hour_integer(v_object, v_reserve)
                        cur.execute(sql_update_log)
                        cur.execute(sql_insert_log)
        elif v_add_type == 's':
            pass
        db.commit()
    print "+" + "-" * 26 + "+" + "-" * 5 + "+" + "-" * 45 + "+"
    end_time = datetime.datetime.now()
    cur.execute("select gpmg.func_log(%s,%s,%s,%s,%s,%s)", ("+PA", begin_time, end_time, 0, "Done.", 0))
    cur.close()
    db.commit()
    db.close()
except Exception, e:
    print 'error'
